Introduction to SQLJ

SQLJ enables you to embed SQL statements into Java™ programs. SQLJ is an ANSI standard developed by a consortium of leading providers of database and application server software including IBM® Corporation, Microsoft Corporation, Sun Microsystems, and Oracle.

The SQLJ translator translates an SQLJ source file into a standard Java source file plus an SQLJ serialized profile that encapsulates information about static SQL in the SQLJ source. The translator converts SQLJ clauses to standard Java statements by replacing the embedded SQL statements with calls to the SQLJ runtime library. An SQLJ customization script binds the SQLJ profile to the database, producing one or more database packages. The Java file is compiled and run (with the packages) on the database. The SQLJ runtime environment consists of an SQLJ runtime library that is implemented in pure Java. The SQLJ runtime library calls the JDBC driver for the target database, such as DB2®.

You can use SQLJ in the workbench to create applications to run with any database that supports SQLJ (for example, DB2 or Oracle). The built-in customization feature only works with DB2.

The workbench has the following SQLJ features:

SQLJ and JDBC

Although both SQLJ and JDBC provide Java applications with the capability to access relational databases, there are several differences between them:

Dynamic SQL versus static SQL

In general, dynamic SQL is more flexible than static SQL, because it does not require SQL statements to be created in advance. Static SQL is more efficient, because the database has done much of the work required to run the SQL statements before run time.

Dynamic SQL provides the ability to query and update tables when, for example, you do not know the number and types of the columns until run time. Using dynamic SQL in JDBC, a Java program can create SQL statements at run time.

With dynamic SQL, database access and authorization are determined at run time. The user of the application must have all required database privileges, and the database must determine the best way to access the required data at run time. However, with static SQL, access and authorization are determined at customization and bind time. The privileges of the user who performs the bind process determine what the application can do. The database determines the best way to access data during customization and bind time, which improves runtime performance.

Many applications do not need to dynamically create SQL statements because the database metadata (such as table and column names) does not change at run time. Such applications can use static SQL, which provides better performance than dynamic SQL.

Advantages of SQLJ

In addition to better performance through using static SQL, SQLJ has the following advantages over JDBC:

JDBC can do one thing that SQLJ cannot do: run dynamic SQL statements. However, if an application needs to use dynamic SQL, you can include some JDBC code in your program with SQLJ clauses.

SQLJ syntax

SQLJ clauses are embedded into Java code. Each clause is preceded by the symbol #sql. The following SQLJ clause is an example of a SELECT statement with a Java variable in the WHERE clause:

#sql [ctx] cursor1 = {SELECT EMP_ACT.EMPNO FROM EMP_ACT WHERE
EMP_ACT.PROJNO = :strProjNo};

SQLJ in DB2

DB2 SQLJ support is based on the SQLJ ANSI standard. The following kinds of SQL constructs can appear in SQLJ programs:


Feedback